﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ISRecord.View.CustomeException;
using ISRecord.View.Properties;

namespace ISRecord.View.Services
{
    internal static class ExcelService
    {
        /// <summary>
        /// Read data from excel sheet.
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"/>
        /// <exception cref="ReadingFromExcelException"/>
 
        internal static DataSet GetExcelSheet(string filePath, string sheetName)
        {
            if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(Resources.NoExcelFileOrSheetMsg);
            }
            string excelExtention = filePath.Substring(filePath.LastIndexOf('.'));
            string connectionString = null;
            switch (excelExtention)
            {
                case ".xls":
                   connectionString  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""";
                   break;
                case ".xlsx": 
                    connectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + @";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""";
                    break;
                default:
                    throw new ReadingFromExcelException(Resources.ReadingFromExcelExceMsg, null);
            }
            string queryString = "SELECT * FROM [" + sheetName + "$]";
            DataSet ds = new DataSet();
            try
            {
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbDataAdapter adaper = new OleDbDataAdapter(queryString, conn))
                    {
                        adaper.Fill(ds, sheetName);
                        adaper.AcceptChangesDuringFill = false;
                    }
                }
                return ds;
            }
            catch(Exception ex)
            {
                
                throw new ReadingFromExcelException(Resources.ReadingFromExcelExceMsg, ex);
            }
        }
    }
}
